﻿var mysql = require('mysql');
var qs = require('querystring');/*将字符串与json串的转换*/
var logger = require('../util/log.js');
var configDB = require('./configDB.js');
var checkout = require('./checkout.js')
function DoMysql() { 
	var client = mysql.createConnection(configDB.getConfigDB());
	//获取用户信息
	this.getUserInfo = function(userid,callback){
		var ret = {"result":false,"info":"","data":""};
		try{
			client.connect();
			client.query('use mbf');
			var sql = "select * from userinfo where userid = ?";
			var values = new Array(userid);
			try{
				client.query(sql,values,function(err,results,fields){
				
					ret.result = true;
					ret.info = "获取用户信息成功";	
					ret.data = results;
					//返回值
					callback(JSON.stringify(ret));	
				});
			}catch(e){
				logger.logger('normal').info("数据库查询出错，错误信息："+err.message+"  "+e.message);
				callback(JSON.stringify(ret));					
			}
		}catch(e){
			ret.result = false;
			ret.info = "系统异常";
			//返回值
			callback(JSON.stringify(ret));	
			logger.logger('normal').info("错误名称："+e.name+"	错误信息："+e.message);
		}finally{
			if(client!==undefined&&client===""){
				client.end();
		}	
		}
	};
	
	//保存用户信息
	this.saveUserInfo = function(userid,username,sex,birthday,phonenum,email,callback){
		var ret = {"result":true,"info":""};
		if(!checkout.checkUserinfo(userid,username,undefined,sex,birthday,phonenum,email)){
			ret.result = false;
			ret.info = "信息填写有误,数据库无法保存";
			callback(JSON.stringify(ret));
			return;
		};
		console.log("123");
		try{
			client.connect();
			client.query('use mbf');
			var values = new Array(username,sex,birthday,phonenum,email,userid);
			var sql = "update userinfo set username=?," +
			" sex=?," + 
			" birthday=?," + 
			" phoneNum=?," + 
			" email=?" + 
			" WHERE userid = ?";
			console.log(sql);
			client.query(sql,values,function(err,results,fields){
				try{
					console.log(results);
					ret.result = true;
					ret.info = "保存用户信息成功";		
					//返回值
					console.log(JSON.stringify(ret));
					callback(JSON.stringify(ret));	
				}catch(e){
					logger.logger('normal').info("数据库查询出错，错误信息："+err.message+"  "+e.message);
					callback(JSON.stringify(ret));						
				}
			});
				
		}catch(e){
			ret.result = false;
			ret.info = "系统异常";
			//返回值
			callback(JSON.stringify(ret));	
			logger.logger('normal').info("错误名称："+e.name+"	错误信息："+e.message);
		}finally{
			if(client!==undefined&&client===""){
				client.end();
		}	
		}
	};
	//删除日记数据
	this.deleteNote = function(noteID,callback){
		var ret = {"result":false,"info":""};
		try{
			client.connect();
			client.query('use mbf');
			var values = new Array(noteID);
			var sql = "update note set isDelete = 1 WHERE myid = ?";			
			client.query(sql,values,function(err,results,fields){
				try{
					ret.result = true;
					ret.info = "删除note成功";		
					//返回值
					callback(JSON.stringify(ret));	
					}catch(e){
						logger.logger('normal').info("数据库查询出错，错误信息："+err.message+"  "+e.message);
						callback(JSON.stringify(ret));						
					}
			});
				

		}catch(e){
			ret.result = false;
			ret.info = "系统异常";
			//返回值
			callback(JSON.stringify(ret));	
			logger.logger('normal').info("错误名称："+e.name+"	错误信息："+e.message);
		}finally{
			if(client!==undefined&&client===""){
				client.end();
		}	
		}
	};
	//保存日记数据
	this.saveNote = function(noteID,content,userid,callback){
		//建立返回值
		var ret = {"result":false,"info":""};
		if(!checkout.checkNote(userid,'1',content,'0')){
			ret.info = "信息填写有误,数据库无法保存";
			callback(JSON.stringify(ret));
		};
		try{
			client.connect();
			client.query('use mbf');
			var sql = "";
			var values ;
			if(noteID){
				values = new Array(content,noteID);
				sql = "update note SET datetime = SYSDATE(),content = ? WHERE myid = ?";
			}else{
				values = new Array(userid,content);
				sql = "insert into note(userid,type,datetime,content) VALUES(?,'1',SYSDATE(),?)";
			}
			
			client.query(sql,values,function(err,results,fields){
				try{
					ret.result = true;
					ret.info = "保存note成功";		
					//返回值
					callback(JSON.stringify(ret));	
				}catch(e){
					logger.logger('normal').info("数据库查询出错，错误信息："+err.message+"  "+e.message);
					callback(JSON.stringify(ret));						
				}
			});
		}catch(e){
			ret.result = false;
			ret.info = "系统异常";
			//返回值
			callback(JSON.stringify(ret));	
			logger.logger('normal').info("错误名称："+e.name+"	错误信息："+e.message);
		}finally{
			if(client!==undefined&&client===""){
				client.end();
		}	
		}
	};
	//获取日记数据
	this.getNote = function(userid,callback){
		//建立返回值
		var ret = {"result":false,"info":"","data":""};
		try{
			client.connect();
			client.query('use mbf');
			var values = new Array(userid);
			var sql = "SELECT myid as noteID ,DATE_FORMAT(datetime,'%Y-%m-%d %H:%i:%s') as datetime,content from note where userid = ? and isDelete <> 1 order by datetime desc";

			client.query(sql,values,function(err,results,fields){
				try{
					ret.result = true;
					ret.data = results;	
					ret.info = "获取note成功";
							
					//返回值
					callback(JSON.stringify(ret));	
				}catch(e){
					logger.logger('normal').info("数据库查询出错，错误信息："+err.message+"  "+e.message);
					callback(JSON.stringify(ret));						
				}
			});
		}catch(e){
			ret.result = false;
			ret.info = "系统异常";
			//返回值
			callback(JSON.stringify(ret));	
			logger.logger('normal').info("错误名称："+e.name+"	错误信息："+e.message);
		}finally{
			if(client!==undefined&&client===""){
				client.end();
		}	
		}
	};
	//登录验证
	this.login = function(userid,password,callback){
		
		
		//建立返回值
		var ret = {"result":false,"info":"登录失败","data":""};
		try{
			logger.logger('normal').info("sql do login");
			client.connect();
			client.query('use mbf');
			var values = [userid,password];
			var sql = "select * from userinfo where userid = ? and password= ?";
			client.query(sql,values,function(err,results,fields){
				try{
					if(results.length==1){
						ret.result = true;
						ret.info = "登录成功";
						ret.data = results;	
						console.log(JSON.stringify(ret));						
					}else{
						ret.result = false;
						ret.info = "用户名或密码错误";
					}
					console.log(JSON.stringify(ret));
					//返回值
					callback(JSON.stringify(ret));
				}catch(e){
					logger.logger('normal').info("数据库查询出错，错误信息："+err.message+"  "+e.message);
					callback(JSON.stringify(ret));
				}				
			});
		}catch(e){
			ret.result = false;
			ret.info = "系统异常, 登录失败";
			
			//返回值
			callback(JSON.stringify(ret));	
			logger.logger('normal').info("数据库连接出错，错误名称："+e.name+"	错误信息："+e.message);
		}finally{
			if(client!==undefined&&client===""){
				client.end();
		}	
		}
	
	};
	//判断用户id是否重复
	this.useridIsExist = function(userid,callback){
		var ret = {"result":true,"info":""};
		try{
		client.connect();
		client.query('use mbf');
		var values = new Array(userid);
		var sql = "select * from userinfo where userid = ? ";
		client.query(sql,values,function(err,results,fields){
			try{
				if(results.length<1){
					ret.result = false;			
				}else{
					ret.result = true;
					ret.info = "用户已经注册";
				}
				//返回值
				callback(JSON.stringify(ret));
			}catch(e){
				logger.logger('normal').info("数据库查询出错，错误信息："+err.message+"  "+e.message);
				callback(JSON.stringify(ret));						
			}
		});
		}catch(e){
			console.log('error');
			ret.result = false;
			ret.info = "系统异常";
			//返回值
			callback(JSON.stringify(ret));	
			logger.logger('normal').info("错误名称："+e.name+"	错误信息："+e.message);
		}finally{
			if(client!==undefined&&client===""){
			client.end();
		}
		}	
		
		
				
	};
	//注册新用户
	this.addUser = function(userid,password,callback){
		//返回值
		var ret = {"result":false,"info":""};
		/*if(!checkout.checkUserinfo(userid,userid,password)){
			ret.info = "信息填写有误,数据库无法保存";
			callback(JSON.stringify(ret));
		};*/
		try{
		client.connect();
		client.query('use mbf');
		var values = new Array(userid,userid,password);
		var sql = "INSERT INTO userinfo set userid=?,username=?,password=?";
		
		client.query(sql,values,function(err,results,fields){
			try{
				if(err){		
					ret.result = false;
					ret.info = '注册失败';				
					
				}else{
					ret.result = true;
					ret.info = '注册成功';
				}	
				//返回值
				callback(JSON.stringify(ret));
			}catch(e){
				logger.logger('normal').info("数据库查询出错，错误信息："+err.message+"  "+e.message);
				callback(JSON.stringify(ret));	
			}			
		});
		}catch(e){
			ret.result = false;
			ret.info = "系统异常";
			//返回值
			callback(JSON.stringify(ret));	
			logger.logger('normal').info("错误名称："+e.name+"	错误信息："+e.message);
		}finally{		
			
			if(client!==undefined&&client===""){
				client.end();
			}	
		}	
	
	};
}
module.exports = DoMysql;